Linking Tables

GUI for Linking Tables

A linking table joins objects from several tables, and in some cases the linking table may require additional information such as the date or the time. The GUI for linking tables requires the following steps:

A complete GUI (or a GUI control) to SELECT each of the objects involved in the linking. When there are few items a drop down list may be used. When there are a lot of items a complete GUI with filters, and search tools must be implemented. Each SELECT GUI (or SELECT control) must return a valid primary key to create the link.
A set of GUI controls (GUI elements) to collect additional information related to the linking.
Create a INSERT SQL statement (or statements) to perform the linking
Execute the INSERT (ExecuteNonQuery)
Inform the user of the results (reviewing how many rows were affected)

Problem 1

In the best_buy database, create the view vw_item_price_date to display the items, their prices and their effective dates.

Problem 2

In the best_buy database, create the view vw_item_price to display the current price of the items (that is the item price on the greatest effective date that is less than the current date or equal to the current date).

Problem 3

In the best_buy database, create the view vw_item_pricex to display the current price of the items and brand name as shown below. You may insert new records with future dates to test the view. For instance, you may insert a record for tomorrow to test the view tomorrow.

Problem 4

Modify the best_buy.sql file to create a stored procedure to move the items from the cart to the buy_sell and buy_sell_detail tables. The stored procedure must:

Begin a transaction
Insert a new record into the buy_sell table for the given employee_id and client_id. Use the variable @@ROWCOUNT to find out the number of rows affected.
Retrieve the automatically generated buy_sell_id using the variable @@IDENTITY
SELECT each record from the cart table that belongs to a given client.
For each record in the cart table, insert a new record into the buy_sell_detail table
Delete the respective record from the cart table
Commit the transaction
Rollback the transaction, if something goes wrong

best_buy.sql

-- ______________________________________________ proc_buy
CREATE PROCEDURE proc_buy
     @in_client_id INT,
     @in_employee_id INT
AS
     DECLARE @item_count INT;
     DECLARE @item_id INT;
     DECLARE @count INT;
     DECLARE @buy_sell_id INT;
     DECLARE @price MONEY;

     SET @item_count = 0;
     SET @item_id = -1;
     SET @count = -1;
     SET @buy_sell_id = -1;

     BEGIN TRANSACTION;--_________________________________________________

     --____________________________________________ INSERT INTO buy_sell
     ...
     SET @count=@@ROWCOUNT;
     ...
     --
     -- Be sure an INDENTITY value was generated
     IF (@@IDENTITY = NULL)
     BEGIN
          ROLLBACK TRANSACTION;
          RETURN 0;
     END
     SET @buy_sell_id = @@IDENTITY; -- _____________Retrieve the automatically generated buy_sell_id

     DECLARE cur_cart CURSOR READ_ONLY FOR
          SELECT item_id, item_count
          FROM cart
          WHERE client_id = @in_client_id;
     --_______________________________________________ Go item by item in the cart
     OPEN cur_cart;
          FETCH NEXT FROM cur_cart INTO @item_id, @item_count;
          WHILE (@@FETCH_STATUS=0)
          BEGIN
               --_____________________________ Get the price
               ...
               --_____________________________ INSERT INTO buy_sell_detail
               ...
               --_____________________________ DELETE FROM cart
               ...
               --_____________________________ GO TO NEXT item
               FETCH NEXT FROM cur_cart INTO @item_id, @item_count;
          END
     CLOSE cur_cart;
     DEALLOCATE cur_cart;
     COMMIT TRANSACTION;--_________________________________________________
     RETURN 1;
GO

Tip

When calling a stored procedure from a program, it is possible to use the value returned by the procedure to monitor the execution of the procedure and find errors.

Cuando un procedimiento almacenado se llama desde un programa, es posible usar el valor regresado por el procedimiento para monitorear la ejecución del procedimiento y encontrar errores.

Tip

To execute a store procedure from a program, you may use the templates. From the Microsoft Visual Studio Toolbox menu: Tools > Add Wintempla Item... > Clipboard Code > Add > SQL Application programming > Stored Procedure .

Para ejecutar un procedimiento almacenado desde un programa, usted debe las plantillas. Desde el menú Microsoft Visual Studio: Tools > Add Wintempla Item... > Clipboard Code > Add > SQL Application programming > Stored Procedure .

Problem 5

Create a Wintempla Dialog application called Store to place an order from the best_buy database. Points to evaluate:

The system has a login dialog to control access
The system displays a list of items
It is possible to select a client
It is possible to place items in the cart
It is possible to see the cart
It is possible to see the total amount of the items in the cart
It is possible to place an order (by calling the proc_buy stored procedure, use the Wintempla template: SQL Stored procedure)

Problem 6

Create a Wintempla Web application called StoreWeb to place an order from the best_buy database. The web site has two pages: Index and CartPage. Do NOT FORGET to include the "CartPage.h" file in the StoreWeb.cpp file using the command #include. The "Add To Cart" button has the click event (it must not be a Submit button). Points to evaluate:

The system requires a login dialog to control access in ALL PAGES
The system displays a list of items (Index page)
It is possible to select a client (Index page)
It is possible to place items in the cart (Index page)
It is possible to see the cart (CartPage page)
It is possible to see the total amount of the items in the cart (CartPage page)
It is possible to place an order (by calling the proc_buy stored procedure, use the Wintempla template: SQL Stored procedure)
The web application was published in a web server, use Basic Authentication.